package cn.ds.general.sql.util;

import cn.ds.general.sql.entity.FieldInfo;
import cn.ds.general.sql.entity.TableInfo;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DatabaseUtil {

    private static final String SQL = "SELECT * FROM ";

    /**
     * 获取数据库连接
     */
    public static Connection getConnection() {
        Connection conn = null;
        try {
            conn = DataSourceConfig.getDataSource().getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 关闭数据库连接
     *
     * @param conn 数据库连接
     */
    public static void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取数据库下的所有表名
     */
    public static List<TableInfo> getTableNames() {
        List<TableInfo> tableInfos = new ArrayList<>();
        Connection conn = getConnection();
        ResultSet rs = null;
        try {
            //获取数据库的元数据
            DatabaseMetaData db = conn.getMetaData();
            System.out.println("conn.getCatalog() = " + conn.getCatalog());
            //从元数据中获取到所有的表名
            rs = db.getTables(conn.getCatalog(), null, null, new String[]{"TABLE"});
            while (rs.next()) {
                TableInfo tableInfo = TableInfo.builder()
                        .tableName(rs.getString("TABLE_NAME"))
                        .tableComment(rs.getString("REMARKS"))
                        .schemaName(rs.getString("TABLE_CAT"))
                        .tableType(rs.getString("TABLE_TYPE"))
                        .build();
                tableInfos.add(tableInfo);
            }
            System.out.println("tableInfos:" + tableInfos);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                closeConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return tableInfos;
    }

    /**
     * 获取表中所有字段名称
     *
     * @param tableName 表名
     * @return 字段名称列表
     */
    public static List<String> getColumnNames(String tableName) {
        List<String> columnNames = new ArrayList<>();
        //与数据库的连接
        Connection conn = getConnection();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName + " where 1=0";
        try {
            pStemt = conn.prepareStatement(tableSql);
            //结果集元数据
            ResultSetMetaData rsmd = pStemt.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnNames.add(rsmd.getColumnName(i + 1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (pStemt != null) {
                try {
                    pStemt.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return columnNames;
    }

    /**
     * 获取表中所有字段类型
     *
     * @param tableName 表名
     * @return  字段类型列表
     */
    public static List<String> getColumnTypes(String tableName) {
        List<String> columnTypes = new ArrayList<>();
        //与数据库的连接
        Connection conn = getConnection();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName + " where 1=0";
        try {
            pStemt = conn.prepareStatement(tableSql);
            //结果集元数据
            ResultSetMetaData rsmd = pStemt.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnTypes.add(rsmd.getColumnTypeName(i + 1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (pStemt != null) {
                try {
                    pStemt.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return columnTypes;
    }

    /**
     * 获取表中字段的所有注释
     *
     * @param tableName 表名
     * @return  字段注释列表
     */
    public static List<String> getColumnComments(String tableName) {
        List<String> columnTypes = new ArrayList<>();
        //与数据库的连接
        Connection conn = getConnection();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        //列名注释集合
        List<String> columnComments = new ArrayList<>();
        ResultSet rs = null;
        try {
            pStemt = conn.prepareStatement(tableSql);
            rs = pStemt.executeQuery("show full columns from " + tableName);
            while (rs.next()) {
                columnComments.add(rs.getString("Comment"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return columnComments;
    }

    public static List<FieldInfo> getColumnInfos(TableInfo tableInfo) {
        String tableName = tableInfo.getTableName();
        //与数据库的连接
        Connection conn = getConnection();
        PreparedStatement pStemt;
        String tableSql = SQL + tableName;
        //列名注释集合
        List<FieldInfo> fieldInfos = new ArrayList<>();
        ResultSet rs = null;
        try {
            pStemt = conn.prepareStatement(tableSql);
            rs = pStemt.executeQuery("show full columns from " + tableName);
            while (rs.next()) {
                FieldInfo fieldInfo = FieldInfo.builder()
                        .columnName(rs.getString("Field"))
                        .type(rs.getString("Type"))
                        .comment(rs.getString("Comment"))
                        .defaultValue(rs.getString("Default"))
                        .tableName(tableName)
                        .nullable(rs.getString("Null"))
                        .key(rs.getString("Key"))
                        .extra(rs.getString("Extra"))
                        .build();
                fieldInfos.add(fieldInfo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return fieldInfos;
    }

    public static void main(String[] args) {
        List<TableInfo> tableNames = getTableNames();
        System.out.println("tableNames:" + tableNames);
        for (TableInfo tableInfo : tableNames) {
//            System.out.println("ColumnNames:" + getColumnNames(tableName));
//            System.out.println("ColumnTypes:" + getColumnTypes(tableName));
//            System.out.println("ColumnComments:" + getColumnComments(tableName));
            List<FieldInfo> fieldInfos = getColumnInfos(tableInfo);
            System.out.println("ColumnInfo:" + fieldInfos);
        }
    }
}